package me.baicai.weixin.identity.repository;

import com.alibaba.fastjson.JSONObject;
import me.baicai.weixin.entity.gcb.Device;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;


@Repository
public interface DeviceRepository extends JpaRepository<Device, Long>
{

    @Query(value = "select d.sn,d.created,d.remark,d.`description`,ifnull(SUM(o.amount),0) as amount,s.shopName from device d LEFT JOIN `order` o on d.sn = o.deviceSN  LEFT JOIN shop s on d.shopId = s.id where s.agentId = ?1 and d.sn like ?2  GROUP BY d.id", nativeQuery = true)
    List<JSONObject> findAgentDeviceByInput(String openid, String Input, Pageable pageable);

    @Query(value = "select d.sn,d.created,d.remark,d.`description`,ifnull(SUM(o.amount),0) as amount,s.shopName from device d LEFT JOIN `order` o on d.sn = o.deviceSN  LEFT JOIN shop s on d.shopId = s.id where s.agentId = ?1 GROUP BY d.id", nativeQuery = true)
    List<JSONObject> findAgentDevice(String openid, Pageable pageable);

    @Query(value = "select d.sn,d.created,d.remark,d.`description`,ifnull(SUM(o.amount),0) as amount,s.shopName from device d LEFT JOIN `order` o on d.sn = o.deviceSN  LEFT JOIN shop s on d.shopId = s.id where s.id = ?1 and d.sn like ?2  GROUP BY d.id", nativeQuery = true)
    List<JSONObject> findShopDeviceByInput(Integer id, String Input, Pageable pageable);

    @Query(value = "select d.sn,d.created,d.remark,d.`description`,ifnull(SUM(o.amount),0) as amount,s.shopName from device d LEFT JOIN `order` o on d.sn = o.deviceSN  LEFT JOIN shop s on d.shopId = s.id where s.id = ?1 GROUP BY d.id", nativeQuery = true)
    List<JSONObject> findShopDevice(Integer id, Pageable pageable);

    @Transactional
    @Modifying
    @Query("update Device set remark = ?2 where sn = ?1")
    int updateDeviceRemark(String sn, String remark);

    @Transactional
    @Modifying
    @Query("update Device set description = ?2 where sn = ?1")
    int updateDeviceDesc(String sn, String remark);

    @Transactional
    @Modifying
    @Query(value = "delete from device where sn = ?1", nativeQuery = true)
    int deleteBySn(String sn);

    boolean existsBySn(String sn);


    @Query(value = "select o.id,o.amount as orderAmount,ifnull(m.amount,0) as amount,s.shopName,ifnull(m.created,o.created) as created from `order` o LEFT JOIN money_log m on m.orderId = o.id LEFT JOIN shop s on o.shopId = s.id where o.deviceSN = ?1", nativeQuery = true)
    List<JSONObject> getDeviceOrder(String sn, Pageable page);

    @Query(value = "select d.sn,d.id, d.created, d.remark,d.`description`, s.address, s.shopName from device d LEFT JOIN shop s on s.id = d.shopId where s.agentId = ?1 or s.salesmanId= ?1 or s.engId = ?1", nativeQuery = true)
    List<JSONObject> getDeviceManage(String openid, Pageable pageable);

    @Query(value = "select d.sn,d.id, d.created, d.remark,d.`description`, s.address, s.shopName from device d LEFT JOIN shop s on s.id = d.shopId and d.sn like ?2 where s.agentId = ?1 or s.salesmanId= ?1 or s.engId = ?1", nativeQuery = true)
    List<JSONObject> getDeviceManage(String openid, String s, Pageable pageable);

    @Query(value = "select f.id,f.created,f.images,f.message,f.sn,f.tel, o.amount , TIMESTAMPDIFF(MINUTE, o.created, f.created) as min ,s.shopName,s.address, d.remark from fault_record f LEFT JOIN `order` o on o.id = f.orderId LEFT JOIN shop s on s.id = f.shopId LEFT JOIN device d on d.sn = o.deviceSN   where f.created between ?3 and date_add(?4, interval 1 day) and f.`status` = ?2 and s.agentId = ?1 or f.created between ?3 and date_add(?4, interval 1 day) and f.`status` = ?2 and s.engId = ?1", nativeQuery = true)
    List<JSONObject> getRecordByStatus(String openid, Integer status, String start, String end, Pageable pageable);

    @Query(value = "select f.id,f.created,f.images,f.message,f.sn,f.tel, o.amount , TIMESTAMPDIFF(MINUTE, o.created, f.created) as min ,s.shopName,s.address, d.remark from fault_record f LEFT JOIN `order` o on o.id = f.orderId LEFT JOIN shop s on s.id = f.shopId LEFT JOIN device d on d.sn = o.deviceSN   where f.created between ?2 and date_add(?3, interval 1 day) and f.`status` = ?1", nativeQuery = true)
    List<JSONObject> getRecordByStatusOK(Integer status,String start, String end, Pageable pageable);

    @Transactional
    @Modifying
    @Query(value = "update fault_record set `status` = ?2, completed = current_timestamp() where id = ?1", nativeQuery = true)
    int repairDevce(Integer id, Integer status);

    Device findBySn(String sn);

    @Transactional
    @Modifying
    @Query(value = "update Device set shopid = ?2 where shopid = ?1")
    int updateShop(Integer oldid,Integer newid);

    @Query(value = "select d.id, d.sn, d.version, d.model, d.remark, d.description, d.created, s.shopName, s.id as shopId, a.`name` as agentName, count(o.id) as orderCount, ifnull(sum(o.amount),0) as orderAmount from device d LEFT JOIN shop s on s.id = d.shopId LEFT JOIN agent a on a.openid = s.agentId LEFT JOIN `order` o on o.deviceSN = d.sn and o.orderStatus in(1,2,3) GROUP BY d.id, d.sn, d.version, d.model, d.remark, d.description,s.shopName, a.`name`", nativeQuery = true)
    List<JSONObject> findByPCDe(Pageable pageable);

    @Query(value = "select d.id, d.sn, d.version, d.model, d.remark, d.description,d.created ,s.shopName, s.id as shopId, a.`name` as agentName, count(o.id) as orderCount, ifnull(sum(o.amount),0) as orderAmount from device d LEFT JOIN shop s on s.id = d.shopId LEFT JOIN agent a on a.openid = s.agentId LEFT JOIN `order` o on o.deviceSN = d.sn and o.orderStatus in(1,2,3) where d.sn like ?1 GROUP BY d.id, d.sn, d.version, d.model, d.remark, d.description,s.shopName, a.`name`", nativeQuery = true)
    List<JSONObject> findByPCSn(String value, Pageable pageable);

    @Query(value = "select d.id, d.sn, d.version, d.model, d.remark, d.description, d.created, s.shopName, s.id as shopId, a.`name` as agentName, count(o.id) as orderCount, ifnull(sum(o.amount),0) as orderAmount from device d LEFT JOIN shop s on s.id = d.shopId LEFT JOIN agent a on a.openid = s.agentId LEFT JOIN `order` o on o.deviceSN = d.sn and o.orderStatus in(1,2,3) where s.shopName like ?1 GROUP BY d.id, d.sn, d.version, d.model, d.remark, d.description,s.shopName, a.`name`", nativeQuery = true)
    List<JSONObject> findByPCShopName(String value, Pageable pageable);

    @Query(value = "select d.id, d.sn, d.version, d.model, d.remark, d.description, d.created, s.shopName, s.id as shopId, a.`name` as agentName, count(o.id) as orderCount, ifnull(sum(o.amount),0) as orderAmount from device d LEFT JOIN shop s on s.id = d.shopId LEFT JOIN agent a on a.openid = s.agentId LEFT JOIN `order` o on o.deviceSN = d.sn and o.orderStatus in(1,2,3) where s.id like ?1 GROUP BY d.id, d.sn, d.version, d.model, d.remark, d.description,s.shopName, a.`name`", nativeQuery = true)
    List<JSONObject> findByPCShopId(String value, Pageable pageable);

    @Query(value = "select d.id, d.sn, d.version, d.model, d.remark, d.description, d.created, s.shopName, s.id as shopId, a.`name` as agentName, count(o.id) as orderCount, ifnull(sum(o.amount),0) as orderAmount from device d LEFT JOIN shop s on s.id = d.shopId LEFT JOIN agent a on a.openid = s.agentId LEFT JOIN `order` o on o.deviceSN = d.sn and o.orderStatus in(1,2,3) where a.`name` like ?1 GROUP BY d.id, d.sn, d.version, d.model, d.remark, d.description,s.shopName, a.`name`", nativeQuery = true)
    List<JSONObject> findByPCAgent(String value, Pageable pageable);

    @Query(value = "select count(1) from device d where d.sn like ?1 ", nativeQuery = true)
    Integer findByPCSnCount(String s);

    @Query(value = "select count(d.id) from device d LEFT JOIN shop s on s.id = d.shopId where s.shopName like ?1", nativeQuery = true)
    Integer findByPCShopNameCount(String s);

    @Query(value = "select count(d.id) from device d LEFT JOIN shop s on s.id = d.shopId where s.id like ?1", nativeQuery = true)
    Integer findByPCShopIdCount(String s);

    @Query(value = "select count(d.id) from device d LEFT JOIN shop s on s.id = d.shopId LEFT JOIN agent a on a.openid = s.agentId where a.`name` like ?1", nativeQuery = true)
    Integer findByPCAgentCount(String s);

    @Query(value = "select count(d.id) from device d LEFT JOIN shop s on s.id = d.shopId LEFT JOIN agent a on a.openid = s.agentId where a.id like ?1", nativeQuery = true)
    Integer findByPCAgentIdCount(String s);

    @Query(value = "select d.id, d.sn, d.version, d.model, d.remark, d.description, d.created, s.shopName, s.id as shopId, a.`name` as agentName, count(o.id) as orderCount, ifnull(sum(o.amount),0) as orderAmount from device d LEFT JOIN shop s on s.id = d.shopId LEFT JOIN agent a on a.openid = s.agentId LEFT JOIN `order` o on o.deviceSN = d.sn and o.orderStatus in(1,2,3) where a.id like ?1 GROUP BY d.id, d.sn, d.version, d.model, d.remark, d.description,s.shopName, a.`name`", nativeQuery = true)
    List<JSONObject> findByPCAgentId(String value, Pageable pageable);

    @Query(value = "select d.id, d.sn, d.version, d.model, d.remark, d.description,d.created ,s.shopName, s.id as shopId, a.`name` as agentName, count(o.id) as orderCount, ifnull(sum(o.amount),0) as orderAmount from device d LEFT JOIN shop s on s.id = d.shopId LEFT JOIN agent a on a.openid = s.agentId LEFT JOIN `order` o on o.deviceSN = d.sn and o.orderStatus in(1,2,3) GROUP BY d.id, d.sn, d.version, d.model, d.remark, d.description,s.shopName, a.`name`", nativeQuery = true)
    List<JSONObject> findAllDevice(Pageable pageable);

}
